/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package metier.JDBC;

import metier.TypeHeure;
import metier.ExceptionMetier;
import metier.ElementListe;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author saad
 */
public class TypeHeureJDBC extends TypeHeure {
      private final static String ID = "ID_TypeHeure";
    private final static String NOM = "Nom_TypeHeure";
    private final static String NOMTABLE = "TYPEHEURE";
    
    private final static String updateQuery = "update "
            + NOMTABLE + " set "
            + NOM + " = ? where "
            + ID +" = ?";
    private final static String selectQuery = "select * from "
            + NOMTABLE;
    private final static String loadQuery = "select * from "
            +NOMTABLE + " where "
            +ID + " = ?";
    private final static String deleteQuery = "DELETE FROM "
            +NOMTABLE + "WHERE "
            +ID + "= ?";
    private final static String idQuery = "SELECT seq_typeheure.nextval  FROM dual";
    private final static String insertQuery = "insert into "+ NOMTABLE + " values(?, ?)";


    public TypeHeureJDBC(String nom) {
        super(nom);
    }

        public TypeHeureJDBC() {
            super();
    }




    protected int createId() throws ExceptionMetier{
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next())
                throw new ExceptionMetier("creating new id failed due to non generated id");
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
            }
             catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }



    @Override
    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
			PreparedStatement psta = con.prepareStatement(insertQuery);
                        if( getId() == 0) setId(createId());
			psta.setInt(1, this.getId());
			psta.setString(2, this.getNom());
			psta.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    /**
     * M�thode qui permet de mettre � jour l'objet dans le syst�me de stockage
     */
    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(updateQuery);
            psta.setString(1, getNom());
            psta.setInt(2, getId());
            int res = psta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }

    /**
     * M�thode qui permet de supprimer l'objet dans le syst�me de stockage
     */
    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(deleteQuery);
            psta.setInt(1, getId());
            psta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    /**
     * M�thode qui permet d'obtenir la liste
     */

    public static ArrayList<ElementListe> select() throws ExceptionMetier {
    try {
        ArrayList<ElementListe> list = new ArrayList<ElementListe>();
    ResultSet rset = null;
    Connection con = ConnectionJDBC.getConnection();
    Statement sta = con.createStatement();
    rset = sta.executeQuery(selectQuery);
    while(rset.next()){
        String nom = rset.getString(NOM);
        int id = rset.getInt(ID);
        ElementListe e = new ElementListe(id,nom);
        list.add(e);
    }
    rset.close();
    sta.close();
    return list;
    } catch (ClassNotFoundException ex) {
    throw new ExceptionMetier("Select failed", ex);
    } catch (SQLException ex) {
    throw new ExceptionMetier("Select failed", ex);
    }
    }


    public void load(int id) throws ExceptionMetier{
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, id);
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != id) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setId(id);
            setNom(rset.getString(NOM));
            rset.close();
            psta.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }
    }
}
